﻿using MathNet.Numerics.Statistics.Mcmc;
using Microsoft.VisualBasic.ApplicationServices;
using NPOI.SS.Formula.Functions;
using NurseStation.DataModels;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NurseStation.Services
{
    public class NurseDayWorkService
    {
        public List<NurseDayWork> GetAll()
        {
            return DatabaseContext.Db.Queryable<NurseDayWork>().ToList();
        }

        public void Insert(NurseDayWork user)
        {
            DatabaseContext.Db.Insertable(user).ExecuteCommand();
        }

        public void Update(NurseDayWork user)
        {
            DatabaseContext.Db.Updateable(user).ExecuteCommand();
        }

        public void Delete(NurseDayWork user)
        {
            DatabaseContext.Db.Deleteable(user).ExecuteCommand();
        }

        public int Updateable(List<NurseDayWork> user)
        {
            return DatabaseContext.Db.Updateable(user).ExecuteCommand();
        }


        public List<long> Insertable(List<NurseDayWork> user)
        {
            return DatabaseContext.Db.Insertable(user).ExecuteReturnSnowflakeIdList();
        }

        /// <summary>
        /// 传入查询条件获取列表数据
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public List<NurseDayWork> GetListByWhere(Expressionable<NurseDayWork> exp)
        {
            return DatabaseContext.Db.Queryable<NurseDayWork>().Where(exp.ToExpression())?.ToList();
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public List<NurseDayWork> GetYearList(string year)
        {
            List<NurseDayWork> yearList = DatabaseContext.Db.Queryable<NurseDayWork>().Where(c=>SqlFunc.Like(c.GROUPNAME, year+"%")).GroupBy(c => c.GROUPNAME).Select((row) => new NurseDayWork()
            {
                GROUPNAME=row.GROUPNAME,
                C1 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C1), 0, int.Parse(row.C1))).ToString(),
                C2 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C2), 0, int.Parse(row.C2))).ToString(),
                C3 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C3), 0, int.Parse(row.C3))).ToString(),
                C4 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C4), 0, int.Parse(row.C4))).ToString(),
                C5 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C5), 0, int.Parse(row.C5))).ToString(),
                C6 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C6), 0, int.Parse(row.C6))).ToString(),
                C7 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C7), 0, int.Parse(row.C7))).ToString(),
                C8 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C8), 0, int.Parse(row.C8))).ToString(),
                C9 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C9), 0, int.Parse(row.C9))).ToString(),
                C10 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C10), 0, int.Parse(row.C10))).ToString(),
                C11 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C11), 0, int.Parse(row.C11))).ToString(),
                C12 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C12), 0, int.Parse(row.C12))).ToString(),
                C13 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C13), 0, int.Parse(row.C13))).ToString(),
                C14 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C14), 0, int.Parse(row.C14))).ToString(),
                C15 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C15), 0, int.Parse(row.C15))).ToString(),
                C16 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C16), 0, int.Parse(row.C16))).ToString(),
                C17 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C17), 0, int.Parse(row.C17))).ToString(),
                C18 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C18), 0, int.Parse(row.C18))).ToString(),
                C19 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C19), 0, int.Parse(row.C19))).ToString(),
                C20 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C20), 0, int.Parse(row.C20))).ToString(),
                C21 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C21), 0, int.Parse(row.C21))).ToString(),
                C22 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C22), 0, int.Parse(row.C22))).ToString(),
                C23 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C23), 0, int.Parse(row.C23))).ToString(),
                C24 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C24), 0, int.Parse(row.C24))).ToString(),
                C25 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C25), 0, int.Parse(row.C25))).ToString(),
                C26 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C26), 0, int.Parse(row.C26))).ToString(),
                C27 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C27), 0, int.Parse(row.C27))).ToString(),
                C28 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C28), 0, int.Parse(row.C28))).ToString(),
                C29 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C29), 0, int.Parse(row.C29))).ToString(),
                C30 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C30), 0, int.Parse(row.C30))).ToString(),
                C31 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C31), 0, int.Parse(row.C31))).ToString(),
                C32 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C32), 0, int.Parse(row.C32))).ToString(),
                C33 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C33), 0, int.Parse(row.C33))).ToString(),
                C34 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C34), 0, int.Parse(row.C34))).ToString(),
                C35 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C35), 0, int.Parse(row.C35))).ToString(),
                C36 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C36), 0, int.Parse(row.C36))).ToString(),
                C37 = SqlFunc.AggregateSum(SqlFunc.IIF<int>(string.IsNullOrEmpty(row.C37), 0, int.Parse(row.C37))).ToString(),
            } ).ToList();

            return yearList;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public bool UpdateByEditCol(NurseDayWork exp)
        {
            if (exp.ID == 0 || string.IsNullOrEmpty(exp.CurEditCol))
            {
                return false;
            }
            int count = DatabaseContext.Db.Updateable(exp).UpdateColumns(exp.CurEditCol).WhereColumns(it => it.ID).ExecuteCommand();
            return count > 0;
        }


        /// <summary>
        /// 获取列配置字典
        /// </summary>
        /// <returns></returns>
        public List<ColShow> GetDictCol()
        {
            return DatabaseContext.Db.Queryable<ColShow>().OrderBy(it => it.Sort, OrderByType.Asc).ToList();
        }
    }
}
